/* Spreadsheet Pipeline
Present a columnar data entry grid and store entered data in a Google spreadsheet.
(Motivation: Google Forms do not offer this kind of data entry.)
We are initially using this for a time sheet application, hence some data entry gadgets are specific.
A control spreadsheet specifies data entry columns (fields). (See format below.)

Note: We present the data entry form via a Google Sites page rather than via a service URL, 
only because we can thereby make it available to people outside our Google Apps domain.

Control spreadsheet format:
  Specifications start in cell A1.
  The first row (ssLabelRow) contains column labels.
    Each label must be unique.
    More accurately, the letters and digits in each label, after other characters are removed, must be unique.
  The second row contains an optional keyword.
    required - a value must be entered before data is submitted
  The third row (ssTypeRow) contains column data entry types.
  Subsequent rows may contain additional information for some types.
  Types:
    text - simple text entry
    number - currently the same as text, may eventually restrict entry to numbers
    date - a drop-down menu of dates for the current week
    select - a drop-down menu
      Cells below the select keyword contain menu entries. A blank entry will be added as the first menu entry.

Initially, we present the data entry grid with a header row and a single data entry row.
Users click a link below the grid to add data entry rows.
Users click a submit button below the grid to add data to the data input spreadsheet.

Singleton data entry fields appear above the grid.
  So far, one singleton is defined and hard-coded. See provider and providerWrap.
  It might be valuable to use a second sheet in the control spreadsheet to specify singletons.
    [Working on this now...]

Tasks -
* Initialize grid for five days of input if a date column named "Day" is present.
* Iron out time zone effects on dates stored in input spreadsheet.
* Establish way to locate control spreadsheet and input spreadsheet.
* Open form with focus in provider name box. [Don't know how. onload?]
* Add Clear button. [Don't know how. Just refresh the browser.]
* Field test.
*/

var controlSpreadsheetId = "ttsJKpzSFCcpaxPH8KbM6MA";
var inputSpreadsheetId = "tyX1gz69VZmebrSM71svlKg";

// Identify rows in the control spreadsheet.
var ssLabelRow = 1;
var ssKeywordRow = 2;
var ssTypeRow = 3;

// This function executes when the associated Google Sites page is requested.
function doGet(e) {
  // Create a UI application.
  var app = UiApp.createApplication();
  
  // Open the control spreadsheet, which specifies data entry columns.
  var ss = SpreadsheetApp.openById(controlSpreadsheetId);
  var sh = ss.getSheetByName('grid');
  var nCols = sh.getLastColumn();
  var colLabels = sh.getRange(ssLabelRow, 1, 1, nCols).getValues()[0];
  var colTypes = sh.getRange(ssTypeRow, 1, 1, nCols).getValues()[0];
  var colKeywords = sh.getRange(ssKeywordRow, 1, 1, nCols).getValues()[0];
  var col;  // loop index
  // Get singleton specifications too.
  var shSing = ss.getSheetByName('singletons');
  var nSing = shSing.getLastColumn();
  var sing;  // loop index, 0-based
  
  // Create a vertical panel to contain everything.
  var outerPanel = app.createVerticalPanel();
  
  // Create singleton data entry widgets, arranged in a table.
  var singTable = app.createGrid(nSing, 2);
  var singWidg;
  var firstSing;  // a reference to the first singleton, so we can focus it later
  for (sing = 0; sing < nSing; sing++) {
    singTable.setText(sing, 0, shSing.getRange(ssLabelRow, sing + 1).getValue());
    singWidg = singWidget(app, shSing, sing);
    singTable.setWidget(sing, 1, singWidg);
    if (sing == 0)
      firstSing = singWidg;
  }
  
  // Create a data entry grid.
  // First create an invisible row counter. (The count includes the header row.)
  var rowCount = app.createTextBox()
      .setName('rowCount')
      .setId('rowCount')
      .setVisible(false)
      .setText("0");
  // Determine how many data entry rows to create;
  var nRows;
  var dayColumn;
  if ((dayColumn = colKeywords.indexOf('weekday')) >= 0 && colTypes[dayColumn] == 'date')
    nRows = 5;
  else
    nRows = 1, dayColumn = -1;
  // Now create the grid.
  var grid = app.createGrid(nRows + 1, nCols)
      .setBorderWidth(1)
      .setStyleAttribute('marginTop', '12pt')
      .setId('grid');
  
  // Set column headers in the first row.
  for (col = 0; col < nCols; col++)
    grid.setWidget(0, col, app.createLabel(colLabels[col]).setStyleAttribute('textAlign', 'center').setStyleAttribute('fontSize', 'small'));
  // Create data entry rows.
  for (var row = 1; row <= nRows; row++) {
    var rowWidgets = widgetsForRow(app, sh, row);
    if (dayColumn >= 0)
      rowWidgets[dayColumn].setSelectedIndex(row);
    for (col = 0; col < nCols; col++)
      grid.setWidget(row, col, rowWidgets[col]);
    rowCount.setText(String(row));
  }
  
  // Create a clickable label that will add a row to the grid.
  var addRowLink = app.createLabel('add row')
      .setStyleAttribute('color', 'blue')
      .setStyleAttribute('cursor', 'pointer')
      .setStyleAttribute('textDecoration', 'underline');
  var addRowHandler = app.createServerClickHandler("addRow");
  addRowLink.addClickHandler(addRowHandler);
  addRowHandler.addCallbackElement(outerPanel);
  
  // Create a submit button.
  var submit = app.createButton('Submit').setStyleAttribute('marginTop', '15px');
  var submitHandler = app.createServerClickHandler("submit");
  submit.addClickHandler(submitHandler);
  submitHandler.addCallbackElement(outerPanel);
  // Add an annotation under the submit button.
  var submitAnnote = app.createLabel('Click the submit button once, then wait for confirmation.')
    .setStyleAttribute('fontStyle', 'italic')
    .setStyleAttribute('fontSize', 'small');
  
  // Put everything together.
  app.add(outerPanel.add(singTable).add(rowCount).add(grid).add(addRowLink).add(submit).add(submitAnnote));

  return app;
}

// Generate and return a list of dates for a calendar week
// (Sun through Sat) that includes a given date.
function thisWeek(includeDate) {
  var msPerDay = 1000 * 60 * 60 * 24;
  // Find Saturday, 12AM.
  var sunday = new Date(includeDate.getTime() - includeDate.getDay() * msPerDay);
  sunday = new Date(sunday.getFullYear(), sunday.getMonth(), sunday.getDate());
  var dates = new Array(7);
  for (var day = 0; day < 7; day++) {
    dates[day] = new Date(sunday.getTime() + day * msPerDay);
  }
  return dates;
}

// Convert a column label to a valid control name.
function validName(label) {
  return label.replace(/\W/g, '_');
}

// Return the input control name for a grid row and column.
// eg, Column_Label_1
function ctlName(sh, gridRow, gridCol) {
  return validName(sh.getRange(ssLabelRow, gridCol + 1).getValue()) + "_" + gridRow;
}

// Return the selected week ending date, if there is a singleton "Week ending" control.
// Otherwise return the ending date (Saturday) of the current week.
function weekEnding(app) {
  var wkEndSelect = app.getElementById('Week_ending');
}

// Create and return a drop-down date menu for a particular column.
function dateMenu(app, sh, gridRow, gridCol) {
  var m = app.createListBox().setName(ctlName(sh, gridRow, gridCol));
  var dates = thisWeek(new Date());
  for (var i = 0; i < dates.length; i++) {
    m.addItem(dates[i].toDateString());
  }
  return m;
}

// Construct and return a custom drop-down menu.
function menu(app, sh, gridRow, gridCol) {
  var ssCol = gridCol + 1;
  var name = sh.getRange(ssLabelRow, ssCol).getValue() + "_" + gridRow;
  // Retrieve menu items from underneath the 'select' keyword in the spreadsheet.
  var ssRow = ssTypeRow + 1;
  var items = [];
  var value;
  while (value = sh.getRange(ssRow++, ssCol).getValue()) {
    items.push(value);
  }
  // Create a list box widget.
  var m = app.createListBox().setName(ctlName(sh, gridRow, gridCol));
  for (var i = 0; i < items.length; i++) {
    if (!i) m.addItem('');  // add a blank item at the top of the list
    m.addItem(items[i]);
  }
  return m;
}

// Create and return a set of data entry widgets for a particular row number in the grid.
function widgetsForRow(app, sh, gridRow) {
  var nCols = sh.getLastColumn();
  var colTypes = sh.getRange(ssTypeRow, 1, 1, nCols).getValues()[0];
  var widgets = new Array(nCols);
  var i;  // widget index, 0-based (ss columns are 1-based, grid columns 0-based)
  for (var i = 0; i < nCols; i++) {
    switch (colTypes[i].toLowerCase()) {
      case 'date':
        widgets[i] = dateMenu(app, sh, gridRow, i).setName(ctlName(sh, gridRow, i));
        break;
      case 'select':
        widgets[i] = menu(app, sh, gridRow, i).setName(ctlName(sh, gridRow, i));
        break;
      case 'text':
      case 'number':
        widgets[i] = app.createTextBox().setName(ctlName(sh, gridRow, i));
        break;
    }
  }
  return widgets;
}

// Return a singleton data entry widget (not including a label).
// sh is the singleton control sheet in the control spreadsheet.
// sing is the zero-based index of the widget we're creating.
function singWidget(app, sh, sing) {
  var singLabel = sh.getRange(ssLabelRow, sing + 1).getValue();
  var singType = sh.getRange(ssTypeRow, sing + 1).getValue();
  switch (singType) {
    default:
      var widg = app.createTextBox().setWidth('20em').setName(validName(singLabel)).setId(validName(singLabel));
      break;
    case 'week ending':
      var widg = app.createListBox().setName(validName(singLabel)).setId(validName(singLabel));
      // Offer 8 weeks prior and two weeks after today.
      var msPerWeek = 1000 * 60 * 60 * 24 * 7;
      for (var week = -7; week <= 2; week++) {
        var day = new Date(Date.now() + week * msPerWeek);
        widg.addItem(thisWeek(day)[6].toDateString());
        widg.setSelectedIndex(7);  // default to the current week
      }
      break;
  }
  return widg;
}

// Callback function for addRowAnchor onclick handler.
// Add a row at the bottom of the data entry grid.
// If it's one of the first five entry rows, preset a date to Mo - Fr.
function addRow(panel) {
  // Get the UI application and our data entry grid.
  var app = UiApp.getActiveApplication();
  msg(app, Session.getTimeZone());
  var grid = app.getElementById('grid');
  var nextRow = Number(panel.parameter.rowCount);
  // Open the control spreadsheet.
  var ss = SpreadsheetApp.openById(controlSpreadsheetId);
  var sh = ss.getSheetByName('grid');
  var nCols = sh.getLastColumn();
  var col;
  // Create and attach a new row of input widgets.
  grid.resize(nextRow + 1, nCols);
  var rowWidgets = widgetsForRow(app, sh, nextRow);
  for (col = 0; col < nCols; col++) {
    grid.setWidget(nextRow, col, rowWidgets[col]);
  }
  // Increment the stored row count.
  app.getElementById('rowCount').setText(String(nextRow + 1));
  return app.close();
}

// Callback function for submit button onclick handler.
function submit(panel) {
  // Reestablish the UI application.
  var app = UiApp.getActiveApplication();
  // Let them know we're working. [Not displayed until we return, however?]
  var statusMsg = app.createLabel('Submitting data. Please wait...');
  app.add(statusMsg);

  // Open the control spreadsheet.
  var controlSs = SpreadsheetApp.openById(controlSpreadsheetId);
  var gridControlSh = controlSs.getSheetByName('grid');
  var singControlSh = controlSs.getSheetByName('singletons');
  var nCols = gridControlSh.getLastColumn();
  var nSing = singControlSh.getLastColumn();
  var colLabels = gridControlSh.getRange(ssLabelRow, 1, 1, nCols).getValues()[0];
  var colNames = colLabels.map(validName);  // control names, without appended row numbers, eg "_1"
  var colTypes = gridControlSh.getRange(ssTypeRow, 1, 1, nCols).getValues()[0];
  var col, sing;  // loop indexes

  // Get singleton values.
  var singValues = [];
  var singVal;
  var singLabel, singName;  // see validName
  var singKeyword;  // 'local', 'required', or ''
  for (sing = 0; sing < nSing; sing++) {
    singKeyword = singControlSh.getRange(ssKeywordRow, sing + 1).getValue();
    if (singKeyword != 'local') {
      singLabel = singControlSh.getRange(ssLabelRow, sing + 1).getValue();
      singName = validName(singLabel);
      singVal = panel.parameter[singName];
      if (singKeyword == 'required' && singVal == '') {
        statusMsg.setText('Data not submitted! You must enter a value for "' + singLabel + '". Please try again.');
        return app.close();
      } else {
        singValues.push(singVal);  // record it
      }
    }
  }

  // Capture values from each grid row.
  var gridRow;  // grid gridRow index
  var rows = [];  // values from each gridRow
  var lastRow;  // index of last row in rows

  function cellValue(col) {
    // Retrieve a cell value from the data input grid, which could be a text string or a date.
    var val;
    switch (colTypes[col]) {
      case 'text':
      case 'number':
      case 'select':
      default:
        val = panel.parameter[colNames[col] + '_' + gridRow];
        break;
      case 'date':
        val = new Date(panel.parameter[colNames[col] + '_' + gridRow]);
        break;
    }
    return val;
  }

  // Process each row of the grid.
  for (gridRow = 1; panel.parameter[colNames[0] + '_' + gridRow] !== undefined; gridRow++) {
    lastRow = rows.push([cellValue(0)]) - 1;  // start a new row record
    for (var col = 1; col < nCols; col++) {  // add remaining columns
      rows[lastRow][col] = cellValue(col);
    }
  }
  
  // Open the data input spreadsheet.
  var inputSs = SpreadsheetApp.openById(inputSpreadsheetId);
  var inputSh = inputSs.getSheets()[0];
  
  // Store data for each input row.
  var submitted = new Date();
  for (var row = 0; row <= lastRow; row++) {
    var values = [submitted];  // values to store, in order
    values = values.concat(singValues, rows[row]);
    inputSh.getRange(inputSh.getLastRow() + 1, 1, 1, values.length).setValues([values]);
  }
  
  statusMsg.setText('Data submitted. Thank you.');
  return app.close();
}

// Add a message to the panel, for debugging.
function msg(app, t) {
  app.add(app.createLabel(t));
}

// Enumerate and print attributes of an object, for debugging.
function listKeys(app, object) {
  msg(app, object.toString());
  var p;
  for (p in object)
    msg(app, p);
}
